package 存储过程基本知识;

import java.sql.*;
import java.text.SimpleDateFormat;

import java.sql.*;
import java.text.SimpleDateFormat;

public class OracleProcedureCall {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxx)))";
        String username = "xxx";
        String password = "xxx";

        Connection conn = null;
        CallableStatement cstmt = null;

        try {
            // 1. 加载驱动（JDBC 4.0 及以上可省略）
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // 2. 建立数据库连接
            conn = DriverManager.getConnection(url, username, password);

            // 3. 预编译存储过程，使用命名参数
            String procedureCall = "{ call xxxxxx(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }";
            cstmt = conn.prepareCall(procedureCall);

            // 4. 设置输入参数
            cstmt.setString("xx1", "1");
            cstmt.setInt("xx2", 2);

            // 处理日期时间
            String dateStr = "2025-03-12 04:31:11"; // 格式：yyyy-MM-dd HH:mm:ss
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            java.util.Date parsedDate = dateFormat.parse(dateStr);
            Timestamp timestamp = new Timestamp(parsedDate.getTime());
            cstmt.setTimestamp("xx3", timestamp);

            cstmt.setString("xx4", "4");
            cstmt.setString("xx5", "5");
            cstmt.setString("xx6", "6");
            cstmt.setString("xx7", "7");

            // 5. 注册输出参数
            cstmt.registerOutParameter("wl_cntr_plac", Types.VARCHAR);
            cstmt.registerOutParameter("wl_new_cntr", Types.VARCHAR);
            cstmt.registerOutParameter("wl_new_cntr_plac", Types.VARCHAR);
            cstmt.registerOutParameter("wl_return", Types.VARCHAR);

            // 6. 执行存储过程
            cstmt.execute();

            // 7. 获取输出参数
            String wl_cntr_plac = cstmt.getString("wl_cntr_plac");
            String wl_new_cntr = cstmt.getString("wl_new_cntr");
            String wl_new_cntr_plac = cstmt.getString("wl_new_cntr_plac");
            String wl_return = cstmt.getString("wl_return");

            System.out.println("wl_cntr_plac: " + wl_cntr_plac);
            System.out.println("wl_new_cntr: " + wl_new_cntr);
            System.out.println("wl_new_cntr_plac: " + wl_new_cntr_plac);
            System.out.println("wl_return: " + wl_return);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 8. 关闭资源
            try {
                if (cstmt != null) cstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}